﻿using ChurchDiary.TableEntity;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace ChurchWebsite.JobDatas
{
    public static class EmailJobData
    {
        
        public static SqlConnection _con = new SqlConnection(String.Format(ConfigurationManager.ConnectionStrings["ChurchDiaryServer"].ConnectionString));

        public static void Connection()
        {
            if (_con.State == ConnectionState.Closed)
            {
                _con.Open();
            }
        }
        public static List<EntityEmail> GetPendingEmail()
        {
            List<EntityEmail> Emails = new List<EntityEmail>();
            try
            {
                Connection();
                using (SqlCommand _cmd = new SqlCommand("[dbo].[GET_Email]", _con))
                {
                    _cmd.CommandType = CommandType.StoredProcedure;
                    _cmd.Parameters.AddWithValue("@Mode", "PENDING");
                    _cmd.Parameters.AddWithValue("@Status", 0);
                    using (IDataReader OReader = _cmd.ExecuteReader())
                    {
                        if (OReader == null)
                        {
                            return Emails;
                        }
                        while (OReader.Read())
                        {
                            EntityEmail Email = new EntityEmail();
                            Email.EmailId = (Guid)OReader["EmailId"];
                            Email.EmailFrom = OReader["EmailFrom"].ToString();
                            Email.EmailToAddress = OReader["EmailToAddress"].ToString();
                            Email.EmailBcc = OReader["EmailBcc"].ToString();
                            Email.EmailCC = OReader["EmailCC"].ToString();
                            Email.EmailSubject = OReader["EmailSubject"].ToString();
                            Email.EmailType = OReader["EmailType"].ToString();
                            Email.EmailContent = OReader["EmailContent"].ToString();
                            Email.EmailCreatedBy = (Guid)OReader["EmailCreatedBy"];
                            Email.EmailCreatedOn = (DateTime)OReader["EmailCreatedOn"];
                            Email.EmaiStatus = (Int32)OReader["EmaiStatus"];
                            Email.EmailSourceId = (Guid)OReader["EmailSourceId"];
                            Emails.Add(Email);
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            _con.Close();
            return Emails;
        }
        public static void UpdateEmailStatus(EntityEmail Email)
        {
            try
            {
                Connection();
                using (SqlCommand _cmd = new SqlCommand("[dbo].[IDU_Email]", _con))
                {
                    _cmd.CommandType = CommandType.StoredProcedure;
                    _cmd.Parameters.AddWithValue("@mode", "UPD");
                    _cmd.Parameters.AddWithValue("@EmailId", Email.EmailId);
                    _cmd.Parameters.AddWithValue("@EmaiStatus", Email.EmaiStatus);
                    _cmd.Parameters.AddWithValue("@EmailSendOn", DateTime.Now);
                    _cmd.ExecuteNonQuery();
                }
            }
            catch (Exception Ex)
            {
            }
        }
        public static EntityNewsletter GetNewsletter(Guid LetterId)
        {
            EntityNewsletter Newsletter = new EntityNewsletter();
            try
            {
                Connection();
                using (SqlCommand _cmd = new SqlCommand("[dbo].[GET_NewsLetter]", _con))
                {
                    _cmd.CommandType = CommandType.StoredProcedure;
                    _cmd.Parameters.AddWithValue("@Mode", "ONE");
                    _cmd.Parameters.AddWithValue("@NewsLetterId", LetterId);
                    using (IDataReader OReader = _cmd.ExecuteReader())
                    {
                        if (OReader == null)
                        {
                            return Newsletter;
                        }
                        while (OReader.Read())
                        {
                            Newsletter.NewsLetterId = (Guid)OReader["NewsLetterId"];
                            Newsletter.NewsLetterSubject = OReader["NewsLetterSubject"].ToString();
                            Newsletter.NewsLetterContent = OReader["NewsLetterContent"].ToString();
                            Newsletter.CreatedOn = (DateTime)OReader["CreatedOn"];
                            Newsletter.IsDeleted = (bool)OReader["IsDeleted"];
                            Newsletter.NewsLetterStatus = (int)OReader["NewsLetterStatus"];
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            _con.Close();
            return Newsletter;
        }
        public static string GetEmailTemplate(string TemplateContent, string EmailType, Guid EmailId)
        {
            string Template = "";
            switch (EmailType)
            {
                case "LOGIN":
                    Template = GetLoginTemplate(TemplateContent, EmailId);
                    break;
                default:
                    Template = "No Template found";
                    break;
            }
            return Template;
        }
        public static string GetLoginTemplate(string TemplateContent, Guid EmailId)
        {
            EntityUser User = GetUserInfo(EmailId);
            TemplateContent = TemplateContent.Replace("[USERNAME]", User.UserName);
            TemplateContent = TemplateContent.Replace("[PASSWORD]", User.Password);
            TemplateContent = TemplateContent.Replace("[NAME]", User.Name);
            EntityOrganizationInfo OrganizationInfo = GetOrganizationInfo();
            TemplateContent = TemplateContent.Replace("[OrganizationName]", OrganizationInfo.NameOfOrganization);
            TemplateContent = TemplateContent.Replace("[OrganizationLogo]", OrganizationInfo.OrganizationLogo);
            TemplateContent = TemplateContent.Replace("[WEBSITE]", OrganizationInfo.OrganizationWebsite);
            EntitySoftwareInfo SoftwareInfo = GetSoftwareInfo();
            TemplateContent = TemplateContent.Replace("[SoftwareLogo]", SoftwareInfo.SoftwareLogo);
            TemplateContent = TemplateContent.Replace("[SoftwareName]", SoftwareInfo.SoftwareName);
            TemplateContent = TemplateContent.Replace("[SuggestionLink]", SoftwareInfo.SoftwareSupportLink);
            return TemplateContent;
        }
        public static EntityUser GetUserInfo(Guid UserId)
        {
            EntityUser User = new EntityUser();
            try
            {
                Connection();
                using (SqlCommand _cmd = new SqlCommand("[dbo].[GET_UserInfo]", _con))
                {
                    _cmd.CommandType = CommandType.StoredProcedure;
                    _cmd.Parameters.AddWithValue("@UserId", UserId);
                    using (IDataReader OReader = _cmd.ExecuteReader())
                    {
                        if (OReader == null)
                        {
                            return User;
                        }
                        while (OReader.Read())
                        {
                            User.Userid = (Guid)OReader["Userid"];
                            User.UserName = OReader["UserName"].ToString();
                            User.Password = OReader["Password"].ToString();
                            User.Emailid = OReader["Emailid"].ToString();
                            User.Name = OReader["Name"].ToString();
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            _con.Close();
            return User;
        }
        public static EntityOrganizationInfo GetOrganizationInfo()
        {
            EntityOrganizationInfo OrganizationInfo = new EntityOrganizationInfo();
            try
            {
                Connection();
                using (SqlCommand _cmd = new SqlCommand("[dbo].[GET_OrganizationInfo]", _con))
                {
                    using (IDataReader OReader = _cmd.ExecuteReader())
                    {
                        if (OReader == null)
                        {
                            return OrganizationInfo;
                        }
                        while (OReader.Read())
                        {
                            OrganizationInfo.NameOfOrganization = OReader["NameOfOrganization"].ToString();
                            OrganizationInfo.OrganizationAbbrivation = OReader["OrganizationAbbrivation"].ToString();
                            OrganizationInfo.OrganizationAddress = OReader["OrganizationAddress"].ToString();
                            OrganizationInfo.OrganizationEmail = OReader["OrganizationEmail"].ToString();
                            OrganizationInfo.OrganizationId = (Guid)OReader["OrganizationId"];
                            OrganizationInfo.OrganizationLogo = OReader["OrganizationLogo"].ToString();
                            OrganizationInfo.OrganizationMobile = OReader["OrganizationMobile"].ToString();
                            OrganizationInfo.OrganizationPhone = OReader["OrganizationPhone"].ToString();
                            OrganizationInfo.OrganizationWebsite = OReader["OrganizationWebsite"].ToString();
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            _con.Close();
            return OrganizationInfo;
        }
        public static EntitySoftwareInfo GetSoftwareInfo()
        {
            EntitySoftwareInfo SoftwareInfo = new EntitySoftwareInfo();
            try
            {
                Connection();
                using (SqlCommand _cmd = new SqlCommand("[dbo].[GET_SoftwareInfo]", _con))
                {
                    using (IDataReader OReader = _cmd.ExecuteReader())
                    {
                        if (OReader == null)
                        {
                            return SoftwareInfo;
                        }
                        while (OReader.Read())
                        {
                            SoftwareInfo.SoftwareId = (Guid)OReader["SoftwareId"];
                            SoftwareInfo.SoftwareLogo = OReader["SoftwareLogo"].ToString();
                            SoftwareInfo.SoftwareName = OReader["SoftwareName"].ToString();
                            SoftwareInfo.SoftwareSupportLink = OReader["SoftwareSupportLink"].ToString();
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            _con.Close();
            return SoftwareInfo;
        }
        public static string GetCommonParameters(string TemplateContent)
        {
            string Result = "";
            Result = TemplateContent.Replace("[Link]", "http://admin.fgpc.in/");
            return Result;
        }
        public static string GetEmailTemplate(EntityEmail email)
        {
            string Template = "";
            switch (email.EmailType)
            {
                case "LOGIN":
                    Template = GetLoginTemplate(email);
                    break;
                default:
                    Template = "No Template found";
                    break;
            }
            return Template;
        }
        public static string GetLoginTemplate(EntityEmail email)
        {
            string Template = "";
            Template = email.EmailContent.ToString();
            EntityUser User = GetUserInfo(email.EmailSourceId);
            Template = Template.Replace("[USERNAME]", User.UserName);
            Template = Template.Replace("[PASSWORD]", User.Password);
            Template = Template.Replace("[NAME]", User.Name);
            EntityOrganizationInfo OrganizationInfo = GetOrganizationInfo();
            Template = Template.Replace("[OrganizationName]", OrganizationInfo.NameOfOrganization);
            Template = Template.Replace("[OrganizationLogo]", OrganizationInfo.OrganizationLogo);
            Template = Template.Replace("[WEBSITE]", OrganizationInfo.OrganizationWebsite);
            EntitySoftwareInfo SoftwareInfo = GetSoftwareInfo();
            Template = Template.Replace("[SoftwareLogo]", SoftwareInfo.SoftwareLogo);
            Template = Template.Replace("[SoftwareName]", SoftwareInfo.SoftwareName);
            Template = Template.Replace("[SuggestionLink]", SoftwareInfo.SoftwareSupportLink);
            Template = Template.Replace("[LINK]", GetApplicationLink());
            Template = Template.Replace("[Email Subject]", email.EmailSubject);
            Template = Template.Replace("[USERID]", email.EmailSourceId.ToString());
            return Template;
        }
        public static string GetApplicationLink()
        {
            bool IsLocal = true;
            string applicationLink = "";
            //applicationLink = "http://localhost:56529/";
            applicationLink = "http://admin.fgpc.in/";
            return applicationLink;
        }
    }
}